/*
 * @Author: error: git config user.name && git config user.email & please set dead value or install git
 * @Date: 2022-10-18 14:21:26
 * @LastEditors: error: git config user.name && git config user.email & please set dead value or install git
 * @LastEditTime: 2022-10-18 21:19:45
 * @FilePath: \数据库渲染\routes\list.js
 * @Description: 这是默认设置,请设置`customMade`, 打开koroFileHeader查看配置 进行设置: https://github.com/OBKoro1/koro1FileHeader/wiki/%E9%85%8D%E7%BD%AE
 */
var express = require('express');
var router = express.Router();
const mysql = require('mysql')

//连接数据库
let connection = mysql.createConnection({
    host: 'localhost',
    port: '3306',
    user: 'root',
    password: '123456',
    database: 'mysql'
})
connection.connect((err) => {
    if (err) {
        console.log('数据库启动失败')
    } else {
        console.log('数据库启动成功')
    }
})

//渲染首页数据
router.get('/list', (req, res) => {
    let sql = `select * from stu_score`
    connection.query(sql, (err, doc) => {
        if (err) {
            console.log('渲染失败')
        } else {
            res.render('list.html', { doc: doc })
        }
    })
})

//实现添加功能
router.get('/add', (req, res) => {
    res.render('add.html')
})
router.post("/add", (req, res) => {//id要选择自增，栏位不能为空，添加的时候没数据会报错
    let { name, sex, age } = req.body;
    // console.log(req.body.name)
    let sql = `insert into stu_score (name,sex,age) values (?,?,?)`;
    connection.query(sql, [req.body.name, req.body.sex, req.body.age], (err) => {
        if (err) {
            res.redirect("/add")
            console.log(err.message)
        } else {
            res.redirect("/list")
        }
    })
})

//删除数据
router.get("/del", (req, res) => {
    let id = req.query.id;
    console.log(id)
    let sql = `delete from stu_score where id=?`;
    connection.query(sql, [id], (err) => {
        if (err) {
            res.send("删除失败")
            console.log(err.message)
        } else {
            res.redirect("/list");
        }
    })
})
//修改数据
router.get("/update", (req, res) => {
    let id = req.query.id;
    let sql = `select * from stu_score where id=?`;
    connection.query(sql, [id], (err, doc) => {
        if (err) {
            res.send("查询失败")
        } else {
            res.render("edit.html", { doc: doc })
        }
    })
})

//处理表单数据
router.post("/update", (req, res) => {
    let { name, sex, age } = req.body;
    let id = req.query.id;
    let sql = `update stu_score set name=?,sex=?,age=? where id=?`;
    connection.query(sql, [req.body.name, req.body.sex, req.body.age, id], (err) => {
        if (err) {
            res.redirect("/update")
            console.log('更新有误')
        } else {
            res.redirect("/list")
            console.log('更新完成')
        }
    })
})

module.exports = router;